package com.iris.live.services.data.repositories.impl;

import org.springframework.stereotype.Repository;

import java.util.Map;

/**
 * @Description:活动定时任务
 * @author: Tony.Zhang
 * @Date: 2017-10-06 10:14
 **/
@Repository("ActivityCrontabRepositoryImpl")
public class ActivityCrontabRepositoryImpl extends CrontabRepositoryImpl {
    public ActivityCrontabRepositoryImpl() {
    }

    @Override
    public void execute(Map<String, Object> context) throws Exception {
        //邀约线索数
        this.updateInvitedClueInfo(context);

        //达成邀约数
        this.updateAchievedClueInfo(context);

        //邀约出席数
        this.updateAchievedClueInfo(context);

        //实际出席总数
        this.updateActualAttendenceInfo(context);

        //新增线索数
        this.updateNewClueInfo(context);

        //试乘试驾数
        this.updateDriveTestInfo(context);

        //二手车评估数
        this.updateUsedCarAppraiseInfo(context);

        //报价数
        this.updateQuotationInfo(context);

        //订单数
        this.updateOrderInfo(context);

        //零售数
        this.updateRetailInfo(context);

        //首次线索进店数
        this.updateFirstEnterInroomInfo(context);

        //再次线索进店数
        this.updateReenterInroomInfo(context);
    }

    /**
     * 邀约线索数
     * @param context
     * @throws Exception
     */
    private void updateInvitedClueInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_group_report_act_info(act_id,kpi_value,kpi_id) " +
                "select 活动编号,COUNT(1) `邀约线索数`, (SELECT `KPI编号` FROM lsh活动kpi a WHERE a.`许可` = 'LSH' AND a.KPI = '邀约线索数') kpi_id FROM `lsh导入名单` WHERE `导入状态` = '0' AND `是否线索` = '是' AND NOT ISNULL(活动编号) GROUP BY 活动编号 " +
                "ON DUPLICATE KEY UPDATE kpi_value=VALUES(kpi_value)";

        super.executeStatement(statement);
    }

    /**
     * 达成邀约数
     * @param context
     * @throws Exception
     */
    private void updateAchievedClueInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_group_report_act_info(act_id,kpi_value,kpi_id) " +
                "   SELECT c.`活动编号`, COUNT(1) `达成邀约数`, (SELECT `KPI编号` FROM lsh活动kpi a WHERE a.`许可` = 'LSH' AND a.KPI = '达成邀约数') kpi_id\n" +
                "   FROM `lsh待办事项` a ,`lsh导入名单` b, `lsh活动表` c " +
                "   WHERE a.`附属编号`=b.`导入编号` AND b.`活动编号`=c.`活动编号` AND a.`待办分类`='预约接待' AND a.`附属编号类型`='线索' " +
                "     AND b.`导入状态`='0' AND b.`是否线索`='是' AND a.`开始时间`>=c.`开始日期` AND a.`结束时间`<=c.`结束日期` " +
                "   GROUP BY c.`活动编号` " +
                "   ON DUPLICATE KEY UPDATE kpi_value=VALUES(kpi_value)";

        super.executeStatement(statement);
    }

    /**
     * 邀约出席数
     * @param context
     * @throws Exception
     */
    private void updateInvitedAttendenceInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_group_report_act_info(act_id,kpi_value,kpi_id) " +
                "   SELECT c.`活动编号`, COUNT(DISTINCT a.`顾客编号`) `邀约出席数`, (SELECT `KPI编号` FROM lsh活动kpi a WHERE a.`许可` = 'LSH' AND a.KPI = '邀约出席数') kpi_id " +
                "   FROM `顾客进店记录表` a, inroom_act b, `lsh活动表` c, `顾客记录汇总` d, `lsh导入名单` e" +
                "   WHERE a.`进店编号`=b.inroomID  AND b.actID=c.`活动编号`  AND a.`进店日期`>=c.`开始日期`  AND a.`进店日期`<=c.`结束日期` AND a.`顾客编号`=d.`顾客编号`\n" +
                "     AND d.`许可`=e.`许可` AND d.`电话号码`=e.`电话号码` AND e.`导入状态`='0' AND e.`是否线索`='是' " +
                "   GROUP BY c.`活动编号` " +
                "   ON DUPLICATE KEY UPDATE kpi_value=VALUES(kpi_value);";

        super.executeStatement(statement);
    }

    /**
     * 实际出席总数
     * @param context
     * @throws Exception
     */
    private void updateActualAttendenceInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_group_report_act_info(act_id,kpi_value,kpi_id) " +
                "   SELECT c.`活动编号`,COUNT(DISTINCT a.`顾客编号`) `实际出席总数`, (SELECT `KPI编号` FROM lsh活动kpi a WHERE a.`许可` = 'LSH' AND a.KPI = '实际出席总数') kpi_id " +
                "   FROM `顾客进店记录表` a, inroom_act b, `lsh活动表` c " +
                "   WHERE a.`进店编号`=b.inroomID AND b.actID=c.`活动编号` AND a.`进店日期`>=c.`开始日期` AND a.`进店日期`<=c.`结束日期` " +
                "   GROUP BY c.`活动编号` " +
                "   ON DUPLICATE KEY UPDATE kpi_value=VALUES(kpi_value);";

        super.executeStatement(statement);
    }

    /**
     * 新增线索数
     * @param context
     * @throws Exception
     */
    private void updateNewClueInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_group_report_act_info(act_id,kpi_value,kpi_id) " +
                "   SELECT  `活动编号`, COUNT(1) `新增线索数`, (SELECT `KPI编号` FROM lsh活动kpi a WHERE a.`许可` = 'LSH' AND a.KPI = '新增线索数') kpi_id " +
                "   FROM `lsh导入名单`" +
                "   WHERE `是否线索` = '是' AND `是否有效线索` = '是' AND `导入状态` IS NOT NULL AND `活动编号` IS NOT NULL" +
                "   GROUP BY 活动编号" +
                "   ON DUPLICATE KEY UPDATE kpi_value=VALUES(kpi_value);";

        super.executeStatement(statement);
    }

    /**
     * 试乘试驾数
     * @param context
     * @throws Exception
     */
    private void updateDriveTestInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_group_report_act_info(act_id,kpi_value,kpi_id)    " +
                "   SELECT c.`活动编号`, COUNT(DISTINCT a.`顾客编号`) `试乘试驾数`, (SELECT `KPI编号` FROM lsh活动kpi a WHERE a.`许可` = 'LSH' AND a.KPI = '试乘试驾数') kpi_id " +
                "   FROM `顾客进店记录表` a, inroom_act b, `lsh活动表` c " +
                "   WHERE a.`进店编号`=b.inroomID AND b.actID=c.`活动编号` AND a.`进店日期`>=c.`开始日期` AND a.`进店日期`<=c.`结束日期` AND a.`是否试驾`='是' " +
                "   GROUP BY c.`活动编号`\n" +
                "   ON DUPLICATE KEY UPDATE kpi_value=VALUES(kpi_value)";

        super.executeStatement(statement);
    }

    /**
     * 二手车评估数
     * @param context
     * @throws Exception
     */
    private void updateUsedCarAppraiseInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_group_report_act_info(act_id,kpi_value,kpi_id) " +
                "   SELECT  c.`活动编号`, COUNT(DISTINCT a.`顾客编号`) `二手车评估数`, (SELECT `KPI编号` FROM lsh活动kpi a WHERE a.`许可` = 'LSH' AND a.KPI = '二手车评估数') kpi_id " +
                "   FROM `顾客进店记录表` a, inroom_act b, `lsh活动表` c " +
                "   WHERE a.`进店编号`=b.inroomID AND b.actID=c.`活动编号` AND a.`进店日期`>=c.`开始日期` AND a.`进店日期`<=c.`结束日期` AND a.`是否评估`='是' " +
                "   GROUP BY c.`活动编号` " +
                "   ON DUPLICATE KEY UPDATE kpi_value=VALUES(kpi_value)";

        super.executeStatement(statement);
    }

    /**
     * 报价数
     * @param context
     * @throws Exception
     */
    private void updateQuotationInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_group_report_act_info(act_id,kpi_value,kpi_id) " +
                "   SELECT  c.`活动编号`, COUNT(DISTINCT a.`顾客编号`) `报价数`, (SELECT `KPI编号` FROM lsh活动kpi a WHERE a.`许可` = 'LSH' AND a.KPI = '报价数') kpi_id " +
                "   FROM `顾客进店记录表` a, inroom_act b, `lsh活动表` c " +
                "   WHERE a.`进店编号`=b.inroomID AND b.actID=c.`活动编号` AND a.`进店日期`>=c.`开始日期` AND a.`进店日期`<=c.`结束日期` AND a.`是否报价`='是' " +
                "   GROUP BY c.`活动编号` " +
                "   ON DUPLICATE KEY UPDATE kpi_value=VALUES(kpi_value) ";

        super.executeStatement(statement);
    }

    /**
     * 订单数
     * @param context
     * @throws Exception
     */
    private void updateOrderInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_group_report_act_info(act_id,kpi_value,kpi_id) " +
                "   SELECT  c.`活动编号`, COUNT(DISTINCT a.`顾客编号`) `订单数`, (SELECT `KPI编号` FROM lsh活动kpi a WHERE a.`许可` = 'LSH' AND a.KPI = '订单数') kpi_id " +
                "   FROM `顾客进店记录表` a, inroom_act b, `lsh活动表` c " +
                "   WHERE a.`进店编号`=b.inroomID AND b.actID=c.`活动编号` AND a.`进店日期`>=c.`开始日期` " +
                "   AND a.`进店日期`<=DATE_ADD(c.`结束日期`, interval + (select c.值 from `itask基础设置` c where c.许可 = 'LSH' and c.设置项目 = '主动集客市场活动延长期') day) " +
                "   AND a.`是否订单`='是'" +
                "   GROUP BY c.`活动编号` " +
                "   ON DUPLICATE KEY UPDATE kpi_value=VALUES(kpi_value) ";

        super.executeStatement(statement);
    }

    /**
     * 零售数
     * @param context
     * @throws Exception
     */
    private void updateRetailInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_group_report_act_info(act_id,kpi_value,kpi_id) " +
                "   SELECT  c.`活动编号`, COUNT(DISTINCT a.`顾客编号`) `零售数`, (SELECT `KPI编号` FROM lsh活动kpi a WHERE a.`许可` = 'LSH' AND a.KPI = '零售数') kpi_id " +
                "   FROM `顾客进店记录表` a, inroom_act b, `lsh活动表` c " +
                "   WHERE a.`进店编号`=b.inroomID AND b.actID=c.`活动编号` AND a.`进店日期`>=c.`开始日期` " +
                "   AND a.`进店日期`<=DATE_ADD(c.`结束日期`, interval + (select c.值 from `itask基础设置` c where c.许可 = 'LSH' and c.设置项目 = '主动集客市场活动延长期') day) " +
                "   AND a.`是否零售`='是'" +
                "   GROUP BY c.`活动编号` " +
                "   ON DUPLICATE KEY UPDATE kpi_value=VALUES(kpi_value) ";

        super.executeStatement(statement);
    }

    /**
     * 首次线索进店数
     * @param context
     * @throws Exception
     */
    private void updateFirstEnterInroomInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_group_report_act_info(act_id,kpi_value,kpi_id) " +
                "   SELECT c.`活动编号`, COUNT(DISTINCT a.`顾客编号`) `线索首次进店数`, (SELECT `KPI编号` FROM lsh活动kpi a WHERE a.`许可` = 'LSH' AND a.KPI = '线索首次进店数') kpi_id " +
                "   FROM `顾客进店记录表` a, inroom_act b, `lsh活动表` c " +
                "   WHERE a.`进店编号`=b.inroomID AND b.actID=c.`活动编号` AND a.`进店日期`>=c.`开始日期`  AND a.`进店日期`<=c.`结束日期` AND a.`是否首次到店`='是' " +
                "   GROUP BY c.`活动编号` " +
                "   ON DUPLICATE KEY UPDATE kpi_value=VALUES(kpi_value) ";

        super.executeStatement(statement);
    }

    /**
     * 再次线索进店数
     * @param context
     * @throws Exception
     */
    private void updateReenterInroomInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_group_report_act_info(act_id,kpi_value,kpi_id) " +
                "   SELECT c.`活动编号`, COUNT(DISTINCT a.`顾客编号`) `线索再次进店数`, (SELECT `KPI编号` FROM lsh活动kpi a WHERE a.`许可` = 'LSH' AND a.KPI = '线索再次进店数') kpi_id " +
                "   FROM `顾客进店记录表` a, inroom_act b, `lsh活动表` c " +
                "   WHERE a.`进店编号`=b.inroomID AND b.actID=c.`活动编号` AND a.`进店日期`>=c.`开始日期`  AND a.`进店日期`<=c.`结束日期` AND a.`是否首次到店`='否' " +
                "   GROUP BY c.`活动编号` " +
                "   ON DUPLICATE KEY UPDATE kpi_value=VALUES(kpi_value) ";

        super.executeStatement(statement);
    }
}
